package org.totoro.jnative;

import lombok.Data;
import org.hibernate.procedure.ProcedureOutputs;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import org.totoro.StartUpApplication;
import org.totoro.entity.OrderInfo;
import org.totoro.entity.UserInfo;

import javax.persistence.*;
import javax.persistence.criteria.*;
import java.util.List;

/**
 * @author daocr
 * @date 2020/2/20
 */
@RunWith(SpringRunner.class)
@SpringBootTest(classes = StartUpApplication.class)
public class EntityManagerQuery {

    @PersistenceContext
    private EntityManager entityManager;


    /**
     * 主键查询
     */
    @Test
    public void queryById() {
        OrderInfo orderInfo = entityManager.find(OrderInfo.class, 1L);
        System.out.println(orderInfo);
    }

    @Test
    public void queryList() {

        //1、构建环境
        CriteriaBuilder builder = entityManager.getCriteriaBuilder();

        // 2、返回值类型
        CriteriaQuery<OrderInfo> query = builder.createQuery(OrderInfo.class);

        // 3、查询的表
        Root<OrderInfo> from = query.from(OrderInfo.class);
        // 4、返回的类型
        query.select(from);

        // 5、过滤条件
        Predicate ge2Id = builder.ge(from.get("id"), 2);
        query.where(ge2Id);
        // 6、排序
        query.orderBy(builder.desc(from.get("id")));

        // 7、分组
        query.groupBy(from.get("id"));

        // 8、执行查询
        TypedQuery<OrderInfo> query1 = entityManager.createQuery(query);

        // 9 、分页查询
        // offset
        query1.setFirstResult(1);
        // 最大返回记录数
        query1.setMaxResults(20);

        List<OrderInfo> resultList = query1.getResultList();

        for (OrderInfo orderInfo : resultList) {
            System.out.println(orderInfo);
        }
    }

    /**
     * 多表查询
     */
    public void join() {

        //1、构建环境
        CriteriaBuilder builder = entityManager.getCriteriaBuilder();

        CriteriaQuery<JoinResult> query = builder.createQuery(JoinResult.class);

        Root<UserInfo> leftFrom = query.from(UserInfo.class);

        Join<Object, Object> rightFrom = leftFrom.join("userInfoExt", JoinType.LEFT);

        query.select(new Selection<JoinResult>() {
            @Override
            public Selection<JoinResult> alias(String name) {
                return null;
            }

            @Override
            public boolean isCompoundSelection() {
                return false;
            }

            @Override
            public List<Selection<?>> getCompoundSelectionItems() {

                return null;
            }

            @Override
            public Class<? extends JoinResult> getJavaType() {
                return null;
            }

            @Override
            public String getAlias() {
                return null;
            }
        });

    }

    /**
     * 调用存储过程
     */
    public void call() {


        StoredProcedureQuery storedProcedureQuery = entityManager.createStoredProcedureQuery("sp_get_poi_house", String.class);

        //1、 参数名称必须要和 存储过程的参数名称一致
        //2、 参数类型
        //3、 输入值，还是输出值
        storedProcedureQuery.registerStoredProcedureParameter("city_id", Integer.class, ParameterMode.IN);
        storedProcedureQuery.registerStoredProcedureParameter("house_ids", String.class, ParameterMode.OUT);

        try {
            storedProcedureQuery.execute();
            // 获取返回值
            Object house_ids = storedProcedureQuery.getOutputParameterValue("house_ids");
        } finally {
            // 释放连接
            storedProcedureQuery.unwrap(ProcedureOutputs.class).release();
        }


    }

    @Data
    public static class JoinResult {

        private String name;
        private Long orderNo;

    }


}
